{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.13---数值计算和统计基础\n",
    "## 常用数学、统计方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   key1  key2 key3\n",
      "a   4.0   1.0    1\n",
      "b   5.0   2.0    2\n",
      "c   3.0   NaN    3\n",
      "d   NaN   4.0    j\n",
      "e   2.0   5.0    k\n",
      "float64 \t float64 \t object\n",
      "------------------------------------------------------------------------------------------\n",
      "key1    3.5\n",
      "key2    3.0\n",
      "dtype: float64 \t <class 'pandas.core.series.Series'>\n",
      "单独统计一列： 3.0\n",
      "------------------------------------------------------------------------------------------\n",
      "a    2.5\n",
      "b    3.5\n",
      "c    3.0\n",
      "d    4.0\n",
      "e    3.5\n",
      "dtype: float64\n",
      "------------------------------------------------------------------------------------------\n",
      "key1   NaN\n",
      "key2   NaN\n",
      "dtype: float64\n",
      "------------------------------------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "# 基本参数：axis、skipna\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({'key1':[4,5,3,np.nan,2],\n",
    "                  'key2':[1,2,np.nan,4,5],\n",
    "                  'key3':[1,2,3,'j','k']},\n",
    "                  index = list('abcde')\n",
    "                 )\n",
    "print(df)\n",
    "print(df['key1'].dtype,'\\t',df['key2'].dtype,'\\t',df['key3'].dtype)\n",
    "print(\"------------------------------------------------------------------------------------------\")\n",
    "\n",
    "\n",
    "m1 = df.mean()\n",
    "print(m1,'\\t',type(m1))\n",
    "print('单独统计一列：',df['key2'].mean())\n",
    "print(\"------------------------------------------------------------------------------------------\")\n",
    "# np.nan ：空值\n",
    "# .mean()计算均值\n",
    "# 只统计数字列      Note:此处务必要注意一下，只统计数字列！！！\n",
    "# 可以通过索引单独统计一列\n",
    "\n",
    "m2 = df.mean(axis = 1)\n",
    "print(m2)\n",
    "print(\"------------------------------------------------------------------------------------------\")\n",
    "# axis参数：默认为0，以列来计算，axis=1，以行来计算，这里就按照行来汇总了\n",
    "\n",
    "\n",
    "m3 = df.mean(skipna = False)\n",
    "print(m3)\n",
    "print(\"------------------------------------------------------------------------------------------\")\n",
    "# skipna参数：是否忽略NaN，默认True，如False，有NaN的列统计结果仍为NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   key1      key2\n",
      "0     0  7.585925\n",
      "1     1  6.148993\n",
      "2     2  0.595201\n",
      "3     3  8.839312\n",
      "4     4  4.307182\n",
      "5     5  0.317115\n",
      "6     6  3.393694\n",
      "7     7  3.005371\n",
      "8     8  7.643854\n",
      "9     9  6.797406\n",
      "--------------------------------------------------------------------------------------\n",
      "key1    10\n",
      "key2    10\n",
      "dtype: int64 → count统计非Na值的数量\n",
      "\n",
      "key1    0.000000\n",
      "key2    0.317115\n",
      "dtype: float64 → min统计最小值\n",
      " 8.839311725234127 → max统计最大值\n",
      "\n",
      "key1    6.750000\n",
      "key2    7.388795\n",
      "Name: 0.75, dtype: float64 → quantile统计分位数，参数q确定位置\n",
      "\n",
      "key1    45.000000\n",
      "key2    48.634051\n",
      "dtype: float64 → sum求和\n",
      "\n",
      "key1    4.500000\n",
      "key2    4.863405\n",
      "dtype: float64 → mean求平均值\n",
      "\n",
      "key1    4.500000\n",
      "key2    5.228087\n",
      "dtype: float64 → median求算数中位数，50%分位数\n",
      "\n",
      "key1    3.027650\n",
      "key2    3.002813\n",
      "dtype: float64 \n",
      " key1    9.166667\n",
      "key2    9.016886\n",
      "dtype: float64 → std,var分别求标准差，方差\n",
      "\n",
      "key1    0.000000\n",
      "key2   -0.346758\n",
      "dtype: float64 → skew样本的偏度\n",
      "\n",
      "key1   -1.200000\n",
      "key2   -1.233228\n",
      "dtype: float64 → kurt样本的峰度\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 主要数学计算方法，可以用于Series和Dataframe(1)\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'key1':np.arange(10),\n",
    "    'key2':np.random.rand(10)*10\n",
    "})\n",
    "print(df)\n",
    "print(\"--------------------------------------------------------------------------------------\")\n",
    "\n",
    "\n",
    "print(df.count(),'→ count统计非Na值的数量\\n')\n",
    "print(df.min(),'→ min统计最小值\\n',df['key2'].max(),'→ max统计最大值\\n')\n",
    "print(df.quantile(q=0.75),'→ quantile统计分位数，参数q确定位置\\n')\n",
    "print(df.sum(),'→ sum求和\\n')\n",
    "print(df.mean(),'→ mean求平均值\\n')\n",
    "print(df.median(),'→ median求算数中位数，50%分位数\\n')\n",
    "print(df.std(),'\\n',df.var(),'→ std,var分别求标准差，方差\\n')\n",
    "print(df.skew(),'→ skew样本的偏度\\n')\n",
    "print(df.kurt(),'→ kurt样本的峰度\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   key1      key2  key1_s     key2_s\n",
      "0     0  7.585925       0   7.585925\n",
      "1     1  6.148993       1  13.734917\n",
      "2     2  0.595201       3  14.330118\n",
      "3     3  8.839312       6  23.169430\n",
      "4     4  4.307182      10  27.476612\n",
      "5     5  0.317115      15  27.793727\n",
      "6     6  3.393694      21  31.187421\n",
      "7     7  3.005371      28  34.192792\n",
      "8     8  7.643854      36  41.836646\n",
      "9     9  6.797406      45  48.634051 --->cumsum样本的累积和\n",
      "\n",
      "   key1      key2  key1_s     key2_s  key1_p         key2_p\n",
      "0     0  7.585925       0   7.585925       0       7.585925\n",
      "1     1  6.148993       1  13.734917       0      46.645794\n",
      "2     2  0.595201       3  14.330118       0      27.763634\n",
      "3     3  8.839312       6  23.169430       0     245.411415\n",
      "4     4  4.307182      10  27.476612       0    1057.031670\n",
      "5     5  0.317115      15  27.793727       0     335.200305\n",
      "6     6  3.393694      21  31.187421       0    1137.567200\n",
      "7     7  3.005371      28  34.192792       0    3418.811786\n",
      "8     8  7.643854      36  41.836646       0   26132.896491\n",
      "9     9  6.797406      45  48.634051       0  177635.897028 --->cumprod样本的累计积\n",
      "\n",
      "   key1      key2  key1_s     key2_s  key1_p         key2_p\n",
      "0   0.0  7.585925     0.0   7.585925     0.0       7.585925\n",
      "1   1.0  7.585925     1.0  13.734917     0.0      46.645794\n",
      "2   2.0  7.585925     3.0  14.330118     0.0      46.645794\n",
      "3   3.0  8.839312     6.0  23.169430     0.0     245.411415\n",
      "4   4.0  8.839312    10.0  27.476612     0.0    1057.031670\n",
      "5   5.0  8.839312    15.0  27.793727     0.0    1057.031670\n",
      "6   6.0  8.839312    21.0  31.187421     0.0    1137.567200\n",
      "7   7.0  8.839312    28.0  34.192792     0.0    3418.811786\n",
      "8   8.0  8.839312    36.0  41.836646     0.0   26132.896491\n",
      "9   9.0  8.839312    45.0  48.634051     0.0  177635.897028 \n",
      "    key1      key2  key1_s    key2_s  key1_p    key2_p\n",
      "0   0.0  7.585925     0.0  7.585925     0.0  7.585925\n",
      "1   0.0  6.148993     0.0  7.585925     0.0  7.585925\n",
      "2   0.0  0.595201     0.0  7.585925     0.0  7.585925\n",
      "3   0.0  0.595201     0.0  7.585925     0.0  7.585925\n",
      "4   0.0  0.595201     0.0  7.585925     0.0  7.585925\n",
      "5   0.0  0.317115     0.0  7.585925     0.0  7.585925\n",
      "6   0.0  0.317115     0.0  7.585925     0.0  7.585925\n",
      "7   0.0  0.317115     0.0  7.585925     0.0  7.585925\n",
      "8   0.0  0.317115     0.0  7.585925     0.0  7.585925\n",
      "9   0.0  0.317115     0.0  7.585925     0.0  7.585925 --->cummax,cummin分别求累计最大值和累计最小值\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 主要数学计算方法，可以用于Series和Dataframe(2)\n",
    "\n",
    "df['key1_s'] = df['key1'].cumsum()\n",
    "df['key2_s'] = df['key2'].cumsum()\n",
    "print(df,'--->cumsum样本的累积和\\n')\n",
    "\n",
    "df['key1_p'] = df['key1'].cumprod()\n",
    "df['key2_p'] = df['key2'].cumprod()\n",
    "print(df,'--->cumprod样本的累计积\\n')\n",
    "\n",
    "print(df.cummax(),'\\n',df.cummin(),'--->cummax,cummin分别求累计最大值和累计最小值\\n')\n",
    "# 会填充key1，和key2的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     a\n",
      "1     b\n",
      "2     c\n",
      "3     d\n",
      "4     a\n",
      "5     f\n",
      "6     b\n",
      "7     h\n",
      "8     c\n",
      "9     j\n",
      "10    d\n",
      "11    l\n",
      "12    e\n",
      "13    n\n",
      "dtype: object\n",
      "['a' 'b' 'c' 'd' 'f' 'h' 'j' 'l' 'e' 'n'] \t <class 'numpy.ndarray'>\n",
      "0    a\n",
      "1    b\n",
      "2    c\n",
      "3    d\n",
      "4    f\n",
      "5    h\n",
      "6    j\n",
      "7    l\n",
      "8    e\n",
      "9    n\n",
      "dtype: object\n",
      "['a' 'b' 'c' 'd' 'e' 'f' 'h' 'j' 'l' 'n']\n"
     ]
    }
   ],
   "source": [
    "# 唯一值：.unique()\n",
    "\n",
    "s = pd.Series(list(\"abcdafbhcjdlen\"))\n",
    "sq = s.unique()\n",
    "print(s)\n",
    "print(sq,'\\t',type(sq))\n",
    "print(pd.Series(sq))\n",
    "# 得到一个唯一值数组\n",
    "# 通过pd.Series重新变成新的Series\n",
    "\n",
    "sq.sort()\n",
    "print(sq)\n",
    "# 重新排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a    2\n",
      "d    2\n",
      "h    1\n",
      "c    2\n",
      "e    1\n",
      "f    1\n",
      "n    1\n",
      "l    1\n",
      "j    1\n",
      "b    2\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# 值计数：.value_counts()\n",
    "\n",
    "sc = s.value_counts(sort = False)                             # 也可以这样写：pd.value_counts(sc, sort = False)\n",
    "print(sc)\n",
    "# 得到一个新的Series，计算出不同值出现的频率\n",
    "# sort参数：排序，默认为True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    10\n",
      "1    11\n",
      "2    12\n",
      "3    13\n",
      "4    14\n",
      "dtype: int32\n",
      "  key1  key2\n",
      "0    a     4\n",
      "1    s     5\n",
      "2    d     6\n",
      "3    c     7\n",
      "4    b     8\n",
      "5    v     9\n",
      "6    a    10\n",
      "7    s    11\n",
      "8    d    12\n",
      "---------------------------------------------------------------------------------\n",
      "0    False\n",
      "1    False\n",
      "2    False\n",
      "3    False\n",
      "4     True\n",
      "dtype: bool\n",
      "    key1   key2\n",
      "0   True  False\n",
      "1  False  False\n",
      "2  False  False\n",
      "3  False  False\n",
      "4  False   True\n",
      "5  False  False\n",
      "6   True  False\n",
      "7  False  False\n",
      "8  False  False\n"
     ]
    }
   ],
   "source": [
    "# 成员资格：.isin()\n",
    "\n",
    "s = pd.Series(np.arange(10,15))\n",
    "df = pd.DataFrame({\n",
    "    'key1':list('asdcbvasd'),\n",
    "    'key2':np.arange(4,13)}\n",
    ")\n",
    "print(s)\n",
    "print(df)\n",
    "print(\"---------------------------------------------------------------------------------\")\n",
    "\n",
    "print(s.isin([5,14]))\n",
    "print(df.isin(['a','bc','10',8]))\n",
    "# 用[]表示\n",
    "# 得到一个布尔值的Series或者Dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.14---文本数据\n",
    "## Pandas针对字符串配备的一套方法，使其易于对数组的每个元素进行操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0          A\n",
      "1          b\n",
      "2          C\n",
      "3    bbhello\n",
      "4        123\n",
      "5        NaN\n",
      "6         hj\n",
      "dtype: object\n",
      "  key1  key2\n",
      "0    a   hee\n",
      "1    b    fv\n",
      "2    c     w\n",
      "3    d  hija\n",
      "4    e   123\n",
      "5    f   NaN\n",
      "-------------------------------------------------------------------------------\n",
      "0    0.0\n",
      "1    1.0\n",
      "2    0.0\n",
      "3    2.0\n",
      "4    0.0\n",
      "5    NaN\n",
      "6    0.0\n",
      "dtype: float64\n",
      "0     HEE\n",
      "1      FV\n",
      "2       W\n",
      "3    HIJA\n",
      "4     123\n",
      "5     NaN\n",
      "Name: key2, dtype: object\n",
      "----------------------------------------------------------------- --------------\n",
      "  KEY1  KEY2\n",
      "0    a   hee\n",
      "1    b    fv\n",
      "2    c     w\n",
      "3    d  hija\n",
      "4    e   123\n",
      "5    f   NaN\n"
     ]
    }
   ],
   "source": [
    "# 通过str访问，且自动排除丢失/NA值\n",
    "\n",
    "s = pd.Series(['A','b','C','bbhello','123',np.nan,'hj'])\n",
    "df = pd.DataFrame({\n",
    "    'key1':list('abcdef'),\n",
    "    'key2':['hee','fv','w','hija','123',np.nan]}\n",
    ")\n",
    "print(s)\n",
    "print(df)\n",
    "print(\"-------------------------------------------------------------------------------\")\n",
    "\n",
    "print(s.str.count('b'))\n",
    "print(df['key2'].str.upper())\n",
    "print(\"----------------------------------------------------------------- --------------\")\n",
    "# 直接通过.str调用字符串方法\n",
    "# 可以对Series、Dataframe使用   NOTE: 注意：它的适用范围还挺广泛！！！\n",
    "# 自动过滤NaN值\n",
    "\n",
    "df.columns = df.columns.str.upper()\n",
    "print(df)\n",
    "# df.columns是一个Index对象，也可使用.str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0          a\n",
      "1          b\n",
      "2    bbhello\n",
      "3        123\n",
      "4        NaN\n",
      "dtype: object → lower小写\n",
      "\n",
      "0          A\n",
      "1          B\n",
      "2    BBHELLO\n",
      "3        123\n",
      "4        NaN\n",
      "dtype: object → upper大写\n",
      "\n",
      "0    1.0\n",
      "1    1.0\n",
      "2    7.0\n",
      "3    3.0\n",
      "4    NaN\n",
      "dtype: float64 → len字符长度\n",
      "\n",
      "0    False\n",
      "1     True\n",
      "2     True\n",
      "3    False\n",
      "4      NaN\n",
      "dtype: object → 判断起始是否为a\n",
      "\n",
      "0    False\n",
      "1    False\n",
      "2    False\n",
      "3     True\n",
      "4      NaN\n",
      "dtype: object → 判断结束是否为3\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 字符串常用方法（1）---lower(),upper(),len(),startwith(),endwith()\n",
    "\n",
    "s = pd.Series(['A','b','bbhello','123',np.nan])\n",
    "\n",
    "print(s.str.lower(),'→ lower小写\\n')\n",
    "print(s.str.upper(),'→ upper大写\\n')\n",
    "print(s.str.len(),'→ len字符长度\\n')\n",
    "print(s.str.startswith('b'),'→ 判断起始是否为a\\n')\n",
    "print(s.str.endswith('3'),'→ 判断结束是否为3\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     jack\n",
      "1     jill\n",
      "2    jesse\n",
      "3    frank\n",
      "dtype: object\n",
      "    Column A    Column B \n",
      "0    0.060315    0.093484\n",
      "1   -2.603031   -0.794296\n",
      "2    0.223535   -0.000218\n",
      "--------------------------------------------------------------------------\n",
      "0     jack\n",
      "1     jill\n",
      "2    jesse\n",
      "3    frank\n",
      "dtype: object\n",
      "0     jack\n",
      "1     jill\n",
      "2    jesse\n",
      "3    frank\n",
      "dtype: object\n",
      "0     jack\n",
      "1     jill\n",
      "2    jesse\n",
      "3    frank\n",
      "dtype: object\n",
      "   Column A  Column B\n",
      "0  0.060315  0.093484\n",
      "1 -2.603031 -0.794296\n",
      "2  0.223535 -0.000218\n"
     ]
    }
   ],
   "source": [
    "# 字符串常用方法(2)---strip()\n",
    "\n",
    "s = pd.Series(['jack','jill','jesse','frank'])\n",
    "df = pd.DataFrame(np.random.randn(3,2),\n",
    "                 index = range(3),\n",
    "                 columns = [' Column A ',' Column B ']\n",
    "                 )\n",
    "print(s)\n",
    "print(df)\n",
    "print(\"--------------------------------------------------------------------------\")\n",
    "\n",
    "print(s.str.strip())                 # 去掉字符串中的空格\n",
    "print(s.str.lstrip())               # 去掉字符串里面左边的空格\n",
    "print(s.str.rstrip())              # 去掉字符串里面右边的空格\n",
    "\n",
    "df.columns = df.columns.str.strip()\n",
    "print(df)\n",
    "# 这里去掉了columns的前后空格，但没有去掉中间空格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Column A    Column B \n",
      "0    0.983065    1.239220\n",
      "1    0.755034   -1.181737\n",
      "2    0.660515    0.580187\n",
      "   -Column-A-  -Column-B-\n",
      "0    0.983065    1.239220\n",
      "1    0.755034   -1.181737\n",
      "2    0.660515    0.580187\n",
      "   heheColumn-A-  heheColumn-B-\n",
      "0       0.983065       1.239220\n",
      "1       0.755034      -1.181737\n",
      "2       0.660515       0.580187\n"
     ]
    }
   ],
   "source": [
    "# 字符串常用方法(3)---replace()\n",
    "\n",
    "df = pd.DataFrame(np.random.randn(3,2),\n",
    "                 index = range(3),\n",
    "                 columns = [' Column A ',' Column B ']\n",
    "                 )\n",
    "print(df)\n",
    "df.columns = df.columns.str.replace(' ','-')\n",
    "print(df)\n",
    "# 替换\n",
    "\n",
    "df.columns = df.columns.str.replace('-','hehe',n = 1)\n",
    "print(df)\n",
    "# n: 替换的个数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    [a, b, c]\n",
      "1    [1, 2, 3]\n",
      "2          NaN\n",
      "3          NaN\n",
      "dtype: object\n",
      "------------------------------------------------------------------------------\n",
      "['a', 'b', 'c']\n",
      "------------------------------------------------------------------------------\n",
      "0      a\n",
      "1      1\n",
      "2    NaN\n",
      "3    NaN\n",
      "dtype: object\n",
      "0      b\n",
      "1      2\n",
      "2    NaN\n",
      "3    NaN\n",
      "dtype: object\n",
      "------------------------------------------------------------------------------\n",
      "     0    1    2\n",
      "0    a    b    c\n",
      "1    1    2    3\n",
      "2  NaN  NaN  NaN\n",
      "3  NaN  NaN  NaN\n",
      "     0    1\n",
      "0    a  b,c\n",
      "1    1  2,3\n",
      "2  NaN  NaN\n",
      "3  NaN  NaN\n",
      "     0    1\n",
      "0  a,b    c\n",
      "1  1,2    3\n",
      "2  NaN  NaN\n",
      "3  NaN  NaN\n",
      "------------------------------------------------------------------------------\n",
      "\n",
      "0    [a, b, c]\n",
      "1    [1, 2, 3]\n",
      "2          NaN\n",
      "Name: key2, dtype: object\n"
     ]
    }
   ],
   "source": [
    "# 字符串常用方法(4)--- split(),rsplit()\n",
    "\n",
    "s = pd.Series(['a,b,c','1,2,3',['a,,,c'],np.nan])\n",
    "print(s.str.split(','))\n",
    "print('------------------------------------------------------------------------------')\n",
    "# 类似字符串的split\n",
    "\n",
    "print(s.str.split(',')[0])\n",
    "print('------------------------------------------------------------------------------')\n",
    "# 直接索引得到一个list\n",
    "\n",
    "print(s.str.split(',').str[0])\n",
    "print(s.str.split(',').str.get(1))\n",
    "print('------------------------------------------------------------------------------')\n",
    "# 可以使用get或[]符号访问拆分列表中的元素\n",
    "\n",
    "print(s.str.split(',',expand = True))\n",
    "print(s.str.split(',',expand = True,n = 1))\n",
    "print(s.str.rsplit(',',expand = True,n = 1))\n",
    "print('------------------------------------------------------------------------------\\n')\n",
    "# 可以使用expand可以轻松扩展此操作以返回DataFrame\n",
    "# n参数限制分割数 --->即相对应的列数。\n",
    "# rsplit类似于split，反向工作，即从字符串的末尾到字符串的开头\n",
    "\n",
    "df = pd.DataFrame({'key1':['a,b,c','1,2,3',[':,., ']],\n",
    "                  'key2':['a-b-c','1-2-3',[':-.- ']]})\n",
    "print(df['key2'].str.split('-'))\n",
    "# Dataframe使用split"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0          A\n",
      "1          b\n",
      "2          C\n",
      "3    bbhello\n",
      "4        123\n",
      "5        NaN\n",
      "6         hj\n",
      "dtype: object\n",
      "  key1  key2\n",
      "0    a   hee\n",
      "1    b    fv\n",
      "2    c     w\n",
      "3    d  hija\n",
      "4    e   123\n",
      "5    f   NaN\n",
      "------------------------------------------------------------------------\n",
      "\n",
      "0      A\n",
      "1      b\n",
      "2      C\n",
      "3      b\n",
      "4      1\n",
      "5    NaN\n",
      "6      h\n",
      "dtype: object\n",
      "0      A\n",
      "1      b\n",
      "2      C\n",
      "3     bb\n",
      "4     12\n",
      "5    NaN\n",
      "6     hj\n",
      "dtype: object\n",
      "0      h\n",
      "1      f\n",
      "2      w\n",
      "3      h\n",
      "4      1\n",
      "5    NaN\n",
      "Name: key2, dtype: object\n"
     ]
    }
   ],
   "source": [
    "# 字符串索引\n",
    "\n",
    "s = pd.Series(['A','b','C','bbhello','123',np.nan,'hj'])\n",
    "df = pd.DataFrame({'key1':list('abcdef'),\n",
    "                  'key2':['hee','fv','w','hija','123',np.nan]})\n",
    "\n",
    "print(s)\n",
    "print(df)\n",
    "print(\"------------------------------------------------------------------------\\n\")\n",
    "print(s.str[0])  # 取第一个字符串\n",
    "print(s.str[:2])  # 取前两个字符串\n",
    "print(df['key2'].str[0]) \n",
    "# str之后和字符串本身索引方式相同"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.15---合并：merge(),join()\n",
    "## Pandas具有全功能的，高性能内存中连接操作，与SQL等关系数据库非常相似\n",
    "\n",
    "## pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None,\n",
    "##         left_index=False, right_index=False, sort=True,\n",
    "##         suffixes=('_x', '_y'), copy=True, indicator=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B key   C   D\n",
      "0  A0  B0  k0  C0  D0\n",
      "1  A1  B1  k1  C1  D1\n",
      "2  A2  B2  k2  C2  D2\n",
      "3  A3  B3  k3  C3  D3\n",
      "-------------------------------------------------------------------------------------------\n",
      "    A   B key1 key2   C   D\n",
      "0  A0  B0   k0   k0  C0  D0\n",
      "1  A2  B2   k1   k0  C1  D1\n",
      "2  A2  B2   k1   k0  C2  D2\n"
     ]
    }
   ],
   "source": [
    "# merge合并，类似excel中的vlookup\n",
    "\n",
    "df1 = pd.DataFrame({\n",
    "    'key':['k0','k1','k2','k3'],\n",
    "    'A':['A0','A1','A2','A3'],\n",
    "    'B':['B0','B1','B2','B3']\n",
    "})\n",
    "df2 = pd.DataFrame({\n",
    "    'key':['k0','k1','k2','k3'],\n",
    "    'C':['C0','C1','C2','C3'],\n",
    "    'D':['D0','D1','D2','D3']\n",
    "})\n",
    "df3 = pd.DataFrame({\n",
    "    'key1':['k0','k0','k1','k2'],\n",
    "    'key2':['k0','k1','k0','k1'],\n",
    "    'A':['A0','A1','A2','A3'],\n",
    "    'B':['B0','B1','B2','B3']\n",
    "})\n",
    "df4 = pd.DataFrame({\n",
    "    'key1':['k0','k1','k1','k2'],\n",
    "    'key2':['k0','k0','k0','k0'],\n",
    "    'C':['C0','C1','C2','C3'],\n",
    "    'D':['D0','D1','D2','D3']\n",
    "})\n",
    "print(pd.merge(df1,df2,on = 'key'))\n",
    "print(\"-------------------------------------------------------------------------------------------\")\n",
    "# left：第一个df\n",
    "# right：第二个df\n",
    "# on：参考键\n",
    "\n",
    "print(pd.merge(df3,df4,on = ['key1','key2']))\n",
    "# 多个连接键的用法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A   B key1 key2   C   D\n",
      "0  A0  B0   k0   k0  C0  D0\n",
      "1  A2  B2   k1   k0  C1  D1\n",
      "2  A2  B2   k1   k0  C2  D2\n",
      "-----------------------------------------------------------------------------------\n",
      "     A    B key1 key2    C    D\n",
      "0   A0   B0   k0   k0   C0   D0\n",
      "1   A1   B1   k0   k1  NaN  NaN\n",
      "2   A2   B2   k1   k0   C1   D1\n",
      "3   A2   B2   k1   k0   C2   D2\n",
      "4   A3   B3   k2   k1  NaN  NaN\n",
      "5  NaN  NaN   k2   k0   C3   D3\n",
      "-----------------------------------------------------------------------------------\n",
      "    A   B key1 key2    C    D\n",
      "0  A0  B0   k0   k0   C0   D0\n",
      "1  A1  B1   k0   k1  NaN  NaN\n",
      "2  A2  B2   k1   k0   C1   D1\n",
      "3  A2  B2   k1   k0   C2   D2\n",
      "4  A3  B3   k2   k1  NaN  NaN\n",
      "-----------------------------------------------------------------------------------\n",
      "     A    B key1 key2   C   D\n",
      "0   A0   B0   k0   k0  C0  D0\n",
      "1   A2   B2   k1   k0  C1  D1\n",
      "2   A2   B2   k1   k0  C2  D2\n",
      "3  NaN  NaN   k2   k0  C3  D3\n",
      "-----------------------------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "# 参数how：合并方式     注意：这里的连接操作和SQL语言的连接操作简直一模一样！！！\n",
    "\n",
    "print(pd.merge(df3,df4,on = ['key1','key2'],how = 'inner'))\n",
    "print(\"-----------------------------------------------------------------------------------\")\n",
    "# inner：默认，取交集\n",
    "\n",
    "print(pd.merge(df3,df4,on = ['key1','key2'],how = 'outer'))\n",
    "print(\"-----------------------------------------------------------------------------------\")\n",
    "# outer：取并集，数据缺失范围NaN\n",
    "\n",
    "print(pd.merge(df3,df4,on = ['key1','key2'],how = 'left'))\n",
    "print(\"-----------------------------------------------------------------------------------\")\n",
    "# left：按照df3为参考合并，数据缺失范围NaN\n",
    "\n",
    "print(pd.merge(df3,df4,on = ['key1','key2'],how = 'right'))\n",
    "print(\"-----------------------------------------------------------------------------------\")\n",
    "# right：按照df4为参考合并，数据缺失范围NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   data1 lkey\n",
      "0      0    b\n",
      "1      1    b\n",
      "2      2    a\n",
      "3      3    c\n",
      "4      4    a\n",
      "5      5    a\n",
      "6      6    b\n",
      "   date2 rkey\n",
      "0      0    a\n",
      "1      1    b\n",
      "2      2    d\n",
      "   data1 lkey  date2 rkey\n",
      "0      0    b      1    b\n",
      "1      1    b      1    b\n",
      "2      6    b      1    b\n",
      "3      2    a      0    a\n",
      "4      4    a      0    a\n",
      "5      5    a      0    a\n",
      "----------------------------------------------------------------------------\n",
      "   data1 key\n",
      "0      0   a\n",
      "1      1   b\n",
      "2      2   c\n",
      "3      3   d\n",
      "4      4   f\n",
      "5      5   e\n",
      "6      6   g\n",
      "   date2\n",
      "a    100\n",
      "b    101\n",
      "c    102\n",
      "d    103\n",
      "e    104\n",
      "   data1 key  date2\n",
      "0      0   a    100\n",
      "1      1   b    101\n",
      "2      2   c    102\n",
      "3      3   d    103\n",
      "5      5   e    104\n"
     ]
    }
   ],
   "source": [
    "# 参数 left_on, right_on, left_index, right_index ---> 当键值不为一个列时，可以单独来设置左键与右键\n",
    "\n",
    "df1 = pd.DataFrame({\n",
    "    'lkey':list('bbacaab'),\n",
    "    'data1':range(7)\n",
    "})\n",
    "df2 = pd.DataFrame({\n",
    "    'rkey':list('abd'),\n",
    "    'date2':range(3)\n",
    "})\n",
    "print(df1)\n",
    "print(df2)\n",
    "print(pd.merge(df1, df2, left_on='lkey', right_on='rkey'))\n",
    "print(\"----------------------------------------------------------------------------\")\n",
    "# df1以‘lkey’为键，df2以‘rkey’为键\n",
    "\n",
    "df1 = pd.DataFrame({\n",
    "    'key':list('abcdfeg'),\n",
    "    'data1':range(7)\n",
    "})\n",
    "df2 = pd.DataFrame({\n",
    "    'date2':range(100,105)},\n",
    "    index = list('abcde')\n",
    ")\n",
    "print(df1)\n",
    "print(df2)\n",
    "print(pd.merge(df1, df2, left_on='key', right_index=True))\n",
    "# df1以‘key’为键，df2以index为键\n",
    "# left_index：为True时，第一个df以index为键，默认False   （注意！！！）\n",
    "# right_index：为True时，第二个df以index为键，默认False  （注意！！！）\n",
    "\n",
    "# 所以left_on, right_on, left_index, right_index可以相互组合：\n",
    "# left_on + right_on, left_on + right_index, left_index + right_on, left_index + right_index  ---> 一共有这么几种组合方式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   data1 key\n",
      "0      1   b\n",
      "1      3   b\n",
      "2      2   a\n",
      "3      4   c\n",
      "4      5   a\n",
      "5      9   a\n",
      "6      7   b\n",
      "   date2 key\n",
      "0     11   a\n",
      "1      2   b\n",
      "2     33   d\n",
      "   data1 key  date2\n",
      "0    1.0   b    2.0\n",
      "1    3.0   b    2.0\n",
      "2    7.0   b    2.0\n",
      "3    2.0   a   11.0\n",
      "4    5.0   a   11.0\n",
      "5    9.0   a   11.0\n",
      "6    4.0   c    NaN\n",
      "7    NaN   d   33.0\n",
      "   data1 key  date2\n",
      "0    2.0   a   11.0\n",
      "1    5.0   a   11.0\n",
      "2    9.0   a   11.0\n",
      "3    1.0   b    2.0\n",
      "4    3.0   b    2.0\n",
      "5    7.0   b    2.0\n",
      "6    4.0   c    NaN\n",
      "7    NaN   d   33.0\n",
      "-------------------------------------------------------------------------\n",
      "   data1 key  date2\n",
      "3    1.0   b    2.0\n",
      "0    2.0   a   11.0\n",
      "4    3.0   b    2.0\n",
      "6    4.0   c    NaN\n",
      "1    5.0   a   11.0\n",
      "5    7.0   b    2.0\n",
      "2    9.0   a   11.0\n",
      "7    NaN   d   33.0\n"
     ]
    }
   ],
   "source": [
    "# 参数 sort\n",
    "\n",
    "df1 = pd.DataFrame({\n",
    "    'key':list('bbacaab'),\n",
    "    'data1':[1,3,2,4,5,9,7] \n",
    "})\n",
    "df2 = pd.DataFrame({\n",
    "    'key':list('abd'),\n",
    "    'date2':[11,2,33]\n",
    "})\n",
    "print(df1)\n",
    "print(df2)\n",
    "x1 = pd.merge(df1,df2,on = 'key',how = 'outer')\n",
    "x2 = pd.merge(df1,df2,on = 'key',sort = True,how = 'outer')\n",
    "print(x1)\n",
    "print(x2)\n",
    "print(\"-------------------------------------------------------------------------\")\n",
    "# sort：按照字典顺序通过 连接键 对结果DataFrame进行排序。默认为False，设置为False会大幅提高性能   因为排序必然会增加时空复杂度！\n",
    "\n",
    "print(x2.sort_values('data1'))\n",
    "# 也可直接用Dataframe的排序方法：sort_values，sort_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A   B\n",
      "K0  A0  B0\n",
      "K1  A1  B1\n",
      "K2  A2  B2\n",
      "     C   D\n",
      "K0  C0  D0\n",
      "K2  C2  D2\n",
      "K3  C3  D3\n",
      "     A   B    C    D\n",
      "K0  A0  B0   C0   D0\n",
      "K1  A1  B1  NaN  NaN\n",
      "K2  A2  B2   C2   D2\n",
      "      A    B    C    D\n",
      "K0   A0   B0   C0   D0\n",
      "K1   A1   B1  NaN  NaN\n",
      "K2   A2   B2   C2   D2\n",
      "K3  NaN  NaN   C3   D3\n",
      "------------------------------------------------------------------------------------------\n",
      "   data1 key\n",
      "0      1   b\n",
      "1      3   b\n",
      "2      2   a\n",
      "3      4   c\n",
      "4      5   a\n",
      "5      9   a\n",
      "6      7   b\n",
      "   date2 key\n",
      "0     11   a\n",
      "1      2   b\n",
      "2     33   d\n",
      "   data1 key_1  date2 key_2\n",
      "0      1     b     11     a\n",
      "1      3     b      2     b\n",
      "2      2     a     33     d\n",
      "   data1 key  date2\n",
      "0      1   b   11.0\n",
      "1      3   b    2.0\n",
      "2      2   a   33.0\n",
      "3      4   c    NaN\n",
      "4      5   a    NaN\n",
      "5      9   a    NaN\n",
      "6      7   b    NaN\n",
      "------------------------------------------------------------------------------------------\n",
      "    A   B key\n",
      "0  A0  B0  K0\n",
      "1  A1  B1  K1\n",
      "2  A2  B2  K0\n",
      "3  A3  B3  K1\n",
      "     C   D\n",
      "K0  C0  D0\n",
      "K1  C1  D1\n",
      "    A   B key   C   D\n",
      "0  A0  B0  K0  C0  D0\n",
      "1  A1  B1  K1  C1  D1\n",
      "2  A2  B2  K0  C0  D0\n",
      "3  A3  B3  K1  C1  D1\n"
     ]
    }
   ],
   "source": [
    "# pd.join()\n",
    "\n",
    "left = pd.DataFrame({\n",
    "    'A':['A0','A1','A2'],\n",
    "    'B':['B0','B1','B2']},\n",
    "    index = ['K0','K1','K2']\n",
    ")\n",
    "right = pd.DataFrame({\n",
    "    'C':['C0','C2','C3'],\n",
    "    'D':['D0','D2','D3']},\n",
    "    index = ['K0','K2','K3']\n",
    ")\n",
    "print(left)\n",
    "print(right)\n",
    "print(left.join(right))\n",
    "print(left.join(right,how = 'outer'))\n",
    "print(\"------------------------------------------------------------------------------------------\")\n",
    "# 等价于：pd.merge(left, right, left_index=True, right_index=True, how='outer')\n",
    "\n",
    "df1 = pd.DataFrame({\n",
    "    'key':list('bbacaab'),\n",
    "    'data1':[1,3,2,4,5,9,7]\n",
    "})\n",
    "df2 = pd.DataFrame({\n",
    "    'key':list('abd'),\n",
    "    'date2':[11,2,33]\n",
    "})\n",
    "print(df1)\n",
    "print(df2)\n",
    "print(pd.merge(df1, df2, left_index=True, right_index=True, suffixes=('_1', '_2')))  \n",
    "print(df1.join(df2['date2']))\n",
    "print(\"------------------------------------------------------------------------------------------\")\n",
    "# suffixes=('_x', '_y')默认\n",
    "\n",
    "left = pd.DataFrame({\n",
    "    'A': ['A0', 'A1', 'A2', 'A3'],\n",
    "    'B': ['B0', 'B1', 'B2', 'B3'],\n",
    "    'key': ['K0', 'K1', 'K0', 'K1']\n",
    "})\n",
    "right = pd.DataFrame({\n",
    "    'C': ['C0', 'C1'],\n",
    "    'D': ['D0', 'D1']},\n",
    "    index=['K0', 'K1']\n",
    ")\n",
    "print(left)\n",
    "print(right)\n",
    "print(left.join(right, on = 'key'))\n",
    "# 等价于pd.merge(left, right, left_on='key', right_index=True, how='left', sort=False);\n",
    "# left的‘key’和right的index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.16---连接与修补：concat, combine_first\n",
    "## 连接---沿轴执行连接操作\n",
    "## pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,\n",
    "##          keys=None, levels=None, names=None, verify_integrity=False,\n",
    "##          copy=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    1\n",
      "1    2\n",
      "2    3\n",
      "0    2\n",
      "1    3\n",
      "2    4\n",
      "dtype: int64\n",
      "a    1\n",
      "b    2\n",
      "c    2\n",
      "d    4\n",
      "e    3\n",
      "h    3\n",
      "dtype: int64\n",
      "---------------------------------------------------------------------------------------\n",
      "     0    1\n",
      "a  1.0  NaN\n",
      "b  NaN  2.0\n",
      "c  2.0  NaN\n",
      "d  NaN  4.0\n",
      "e  NaN  3.0\n",
      "h  3.0  NaN\n",
      "---------------------------------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "# 连接：concat\n",
    "\n",
    "s1 = pd.Series([1,2,3])\n",
    "s2 = pd.Series([2,3,4])\n",
    "s3 = pd.Series([1,2,3],index = ['a','c','h'])\n",
    "s4 = pd.Series([2,3,4],index = ['b','e','d'])\n",
    "print(pd.concat([s1,s2]))\n",
    "print(pd.concat([s3,s4]).sort_index())\n",
    "print(\"---------------------------------------------------------------------------------------\")\n",
    "# 默认情况下有axis = 0，即为：行 + 行\n",
    "\n",
    "print(pd.concat([s3,s4],axis = 1))\n",
    "print(\"---------------------------------------------------------------------------------------\")\n",
    "# axis = 1,即为列 + 列，成为了一个Dateframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     0    1\n",
      "a  1.0  NaN\n",
      "b  2.0  2.0\n",
      "c  3.0  3.0\n",
      "d  NaN  4.0\n",
      "   0  1\n",
      "b  2  2\n",
      "c  3  3\n",
      "     0    1\n",
      "a  1.0  NaN\n",
      "b  2.0  2.0\n",
      "d  NaN  4.0\n"
     ]
    }
   ],
   "source": [
    "# 连接方式：join, join_axes\n",
    "\n",
    "s5 = pd.Series([1,2,3],index = list('abc'))\n",
    "s6 = pd.Series([2,3,4],index = list('bcd'))\n",
    "print(pd.concat([s5,s6],axis = 1))\n",
    "print(pd.concat([s5,s6],axis = 1,join = 'inner'))\n",
    "print(pd.concat([s5,s6],axis = 1,join_axes = [['a','b','d']]))\n",
    "# join：{'inner'，'outer'}，默认为“outer”。如何处理其他轴上的索引。outer为联合和inner为交集。\n",
    "# join_axes：指定联合的index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "one  a    1\n",
      "     b    2\n",
      "     c    3\n",
      "two  b    2\n",
      "     c    3\n",
      "     d    4\n",
      "dtype: int64 \t <class 'pandas.core.series.Series'>\n",
      "MultiIndex(levels=[['one', 'two'], ['a', 'b', 'c', 'd']],\n",
      "           labels=[[0, 0, 0, 1, 1, 1], [0, 1, 2, 1, 2, 3]])\n",
      "-----------------------------------------------------------------------------\n",
      "   one  two\n",
      "a  1.0  NaN\n",
      "b  2.0  2.0\n",
      "c  3.0  3.0\n",
      "d  NaN  4.0 \t <class 'pandas.core.frame.DataFrame'>\n"
     ]
    }
   ],
   "source": [
    "# 覆盖列名\n",
    "\n",
    "sre = pd.concat([s5,s6],keys = ['one','two'])\n",
    "print(sre,'\\t',type(sre))\n",
    "print(sre.index)\n",
    "print(\"-----------------------------------------------------------------------------\")\n",
    "# keys：序列，默认值无。使用传递的键作为最外层构建层次索引\n",
    "\n",
    "sre = pd.concat([s5,s6],axis = 1,keys = ['one','two'])\n",
    "print(sre,'\\t',type(sre))\n",
    "# axis = 1,覆盖列名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     0    1    2\n",
      "0  NaN  3.0  5.0\n",
      "1 -4.6  NaN  NaN\n",
      "2  NaN  7.0  NaN\n",
      "      0    1    2\n",
      "1 -42.6  NaN -8.2\n",
      "2  -5.0  1.6  4.0\n",
      "     0    1    2\n",
      "0  NaN  3.0  5.0\n",
      "1 -4.6  NaN -8.2\n",
      "2 -5.0  7.0  4.0\n",
      "---------------------------------------------------------------------------------------\n",
      "      0    1    2\n",
      "0   NaN  3.0  5.0\n",
      "1 -42.6  NaN -8.2\n",
      "2  -5.0  1.6  4.0\n"
     ]
    }
   ],
   "source": [
    "# 修补：combine_first()\n",
    "\n",
    "df1 = pd.DataFrame([[np.nan, 3., 5.], [-4.6, np.nan, np.nan],[np.nan, 7., np.nan]])\n",
    "df2 = pd.DataFrame([[-42.6, np.nan, -8.2], [-5., 1.6, 4]],index=[1, 2])\n",
    "print(df1)\n",
    "print(df2)\n",
    "print(df1.combine_first(df2))\n",
    "print(\"---------------------------------------------------------------------------------------\")\n",
    "# 根据index，df1的空值被df2替代\n",
    "# 如果df2的index多于df1，则更新到df1上，比如index=['a',1]\n",
    "\n",
    "df1.update(df2)\n",
    "print(df1)\n",
    "# update，直接df2覆盖df1，相同index位置"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.17---去重及替换\n",
    "## .duplicated / .replace"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     False\n",
      "1      True\n",
      "2      True\n",
      "3      True\n",
      "4     False\n",
      "5      True\n",
      "6      True\n",
      "7     False\n",
      "8     False\n",
      "9     False\n",
      "10     True\n",
      "11     True\n",
      "12     True\n",
      "dtype: bool\n",
      "0    1\n",
      "4    2\n",
      "7    3\n",
      "8    4\n",
      "9    5\n",
      "dtype: int64\n",
      "-------------------------------------------------------------------\n",
      "0    1\n",
      "4    2\n",
      "7    3\n",
      "8    4\n",
      "9    5\n",
      "dtype: int64\n",
      "-------------------------------------------------------------------\n",
      "0    False\n",
      "1     True\n",
      "2    False\n",
      "3    False\n",
      "4    False\n",
      "dtype: bool\n",
      "0    False\n",
      "1     True\n",
      "2    False\n",
      "3     True\n",
      "4    False\n",
      "Name: key2, dtype: bool\n"
     ]
    }
   ],
   "source": [
    "# 去重：.duplicated\n",
    "\n",
    "s = pd.Series([1,1,1,1,2,2,2,3,4,5,5,5,5])\n",
    "print(s.duplicated())\n",
    "print(s[s.duplicated() == False])\n",
    "print(\"-------------------------------------------------------------------\")\n",
    "# 判断是否重复\n",
    "# 通过布尔判断，得到不重复的值\n",
    "\n",
    "s_re = s.drop_duplicates()\n",
    "print(s_re)\n",
    "print(\"-------------------------------------------------------------------\")\n",
    "# drop.duplicates移除重复\n",
    "# inplace参数：是否替换原值，默认False\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'key1':['a','a',3,4,5],\n",
    "    'key2':['a','a','b','b','c']\n",
    "})\n",
    "print(df.duplicated())\n",
    "print(df['key2'].duplicated())\n",
    "# Dataframe中使用duplicated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    a\n",
      "1    s\n",
      "2    c\n",
      "3    a\n",
      "4    a\n",
      "5    z\n",
      "6    s\n",
      "7    d\n",
      "dtype: object\n",
      "---------------------------------------------------------\n",
      "0    NaN\n",
      "1      s\n",
      "2      c\n",
      "3    NaN\n",
      "4    NaN\n",
      "5      z\n",
      "6      s\n",
      "7      d\n",
      "dtype: object\n",
      "0    NaN\n",
      "1    NaN\n",
      "2      c\n",
      "3    NaN\n",
      "4    NaN\n",
      "5      z\n",
      "6    NaN\n",
      "7      d\n",
      "dtype: object\n",
      "0    hello world!\n",
      "1             123\n",
      "2               c\n",
      "3    hello world!\n",
      "4    hello world!\n",
      "5               z\n",
      "6             123\n",
      "7               d\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# 替换：.replace\n",
    "\n",
    "s = pd.Series(list('ascaazsd'))\n",
    "print(s)\n",
    "print(\"---------------------------------------------------------\")\n",
    "print(s.replace('a',np.nan))\n",
    "print(s.replace(['a','s'],np.nan))\n",
    "print(s.replace({'a':'hello world!','s':123}))\n",
    "# 可一次性替换一个值或多个值\n",
    "# 可传入列表或字典--->这里要注意字典的替换方法！！！"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.18---数据分组\n",
    "## 分组统计：groupby功能\n",
    "* 1.根据某些条件将数据拆分成组\n",
    "* 2.对每个组独立应用函数\n",
    "* 3.将结果合并到一个数据结构中\n",
    "## Dataframe在行（axis=0）或列（axis=1）上进行分组，将一个函数应用到各个分组并产生一个新值，然后函数执行结果被合并到最终的结果对象中。\n",
    "\n",
    "## df.groupby(by=None, axis=0, level=None, as_index=True, sort=True, group_keys=True, squeeze=False, **kwargs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A      B         C         D\n",
      "0  foo    one  0.031570  0.750950\n",
      "1  bar    one -1.558213 -0.757148\n",
      "2  foo    two -0.512195  0.713538\n",
      "3  bar  three  0.207306 -0.590057\n",
      "4  foo    two -0.335273 -1.149204\n",
      "5  bar    two -0.331657  0.291651\n",
      "6  foo    one -0.296271  1.141596\n",
      "7  foo  three  0.654098  0.819153\n",
      "--------------------------------------------------------------------------------------------\n",
      "<pandas.core.groupby.DataFrameGroupBy object at 0x00000151943E8630> \t <class 'pandas.core.groupby.DataFrameGroupBy'>\n",
      "--------------------------------------------------------------------------------------------\n",
      "            C         D\n",
      "A                      \n",
      "bar -0.560855 -0.351851\n",
      "foo -0.091614  0.455206 <class 'pandas.core.frame.DataFrame'> \t Index(['C', 'D'], dtype='object')\n",
      "                  C         D\n",
      "A   B                        \n",
      "bar one   -1.558213 -0.757148\n",
      "    three  0.207306 -0.590057\n",
      "    two   -0.331657  0.291651\n",
      "foo one   -0.132351  0.946273\n",
      "    three  0.654098  0.819153\n",
      "    two   -0.423734 -0.217833 <class 'pandas.core.frame.DataFrame'> \t Index(['C', 'D'], dtype='object')\n",
      "A\n",
      "bar   -0.351851\n",
      "foo    0.455206\n",
      "Name: D, dtype: float64 \t <class 'pandas.core.series.Series'>\n"
     ]
    }
   ],
   "source": [
    "# 分组\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'A':['foo', 'bar', 'foo', 'bar','foo', 'bar', 'foo', 'foo'],\n",
    "    'B':['one', 'one', 'two', 'three', 'two', 'two', 'one', 'three'],\n",
    "    'C':np.random.randn(8),\n",
    "    'D':np.random.randn(8)\n",
    "})\n",
    "print(df)\n",
    "print(\"--------------------------------------------------------------------------------------------\")\n",
    "\n",
    "print(df.groupby('A'),'\\t',type(df.groupby('A')))\n",
    "print(\"--------------------------------------------------------------------------------------------\")\n",
    "# 直接分组得到一个groupby对象，是一个中间数据，没有进行计算\n",
    "\n",
    "a = df.groupby('A').mean()\n",
    "b = df.groupby(['A','B']).mean()\n",
    "c = df.groupby(['A'])['D'].mean()        # 以A为分组，计算D的平均值\n",
    "print(a,type(a),'\\t',a.columns)\n",
    "print(b,type(b),'\\t',b.columns)\n",
    "print(c,'\\t',type(c))\n",
    "# 通过分组后的计算，得到一个新的Dataframe\n",
    "# 默认axis = ，以行来进行分组\n",
    "# 可以单个或者多个（[]）列来进行分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   X  Y\n",
      "0  A  1\n",
      "1  B  4\n",
      "2  A  3\n",
      "3  B  2\n",
      "<pandas.core.groupby.DataFrameGroupBy object at 0x000001518C2E9A58> \t <class 'pandas.core.groupby.DataFrameGroupBy'>\n",
      "----------------------------------------------------------------------------------\n",
      "[('A',    X  Y\n",
      "0  A  1\n",
      "2  A  3), ('B',    X  Y\n",
      "1  B  4\n",
      "3  B  2)] → 可迭代对象，直接生成list\n",
      "\n",
      "('A',    X  Y\n",
      "0  A  1\n",
      "2  A  3) --->以元组形式显示\n",
      "\n",
      "A\n",
      "   X  Y\n",
      "0  A  1\n",
      "2  A  3\n",
      "#####\n",
      "B\n",
      "   X  Y\n",
      "1  B  4\n",
      "3  B  2\n",
      "#####\n",
      "----------------------------------------------------------------------------------\n",
      "   X  Y\n",
      "0  A  1\n",
      "2  A  3 \n",
      "\n",
      "   X  Y\n",
      "1  B  4\n",
      "3  B  2 \n",
      "\n",
      "----------------------------------------------------------------------------------\n",
      "{'A': Int64Index([0, 2], dtype='int64'), 'B': Int64Index([1, 3], dtype='int64')}\n",
      "Int64Index([0, 2], dtype='int64')\n",
      "----------------------------------------------------------------------------------\n",
      "X\n",
      "A    2\n",
      "B    2\n",
      "dtype: int64 \t <class 'pandas.core.series.Series'>\n",
      "----------------------------------------------------------------------------------\n",
      "     A      B         C         D\n",
      "0  foo    one -1.113931 -1.251859\n",
      "1  bar    one -0.851568 -0.513997\n",
      "2  foo    two  1.527962 -1.366965\n",
      "3  bar  three -0.225999 -0.927463\n",
      "4  foo    two -0.368616 -1.629919\n",
      "5  bar    two  0.935503  0.632194\n",
      "6  foo    one -0.649517 -0.683885\n",
      "7  foo  three -1.163829  1.134741\n",
      "{('bar', 'one'): Int64Index([1], dtype='int64'), ('bar', 'three'): Int64Index([3], dtype='int64'), ('bar', 'two'): Int64Index([5], dtype='int64'), ('foo', 'one'): Int64Index([0, 6], dtype='int64'), ('foo', 'three'): Int64Index([7], dtype='int64'), ('foo', 'two'): Int64Index([2, 4], dtype='int64')}\n",
      "Int64Index([7], dtype='int64')\n"
     ]
    }
   ],
   "source": [
    "# 分组：可迭代对象\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'X':['A','B','A','B'],\n",
    "    'Y':[1,4,3,2]\n",
    "})\n",
    "print(df)\n",
    "print(df.groupby(['X']),'\\t',type(df.groupby(['X'])))\n",
    "print(\"----------------------------------------------------------------------------------\")\n",
    "\n",
    "print(list(df.groupby(['X'])),'→ 可迭代对象，直接生成list\\n')\n",
    "print(list(df.groupby(['X']))[0],'--->以元组形式显示\\n')\n",
    "for n,g in df.groupby(['X']):\n",
    "    print(n)\n",
    "    print(g)\n",
    "    print(\"#####\")\n",
    "print(\"----------------------------------------------------------------------------------\")\n",
    "# n是组名，g是分组后的Dataframe\n",
    "\n",
    "print(df.groupby(['X']).get_group('A'),'\\n')\n",
    "print(df.groupby(['X']).get_group('B'),'\\n')\n",
    "print(\"----------------------------------------------------------------------------------\")\n",
    "# .get_group()提取分组后的组\n",
    "\n",
    "grouped = df.groupby(['X'])\n",
    "print(grouped.groups)\n",
    "print(grouped.groups['A'])             # 也可以写成：df.groupby('X').groups['A']\n",
    "print(\"----------------------------------------------------------------------------------\")\n",
    "# .groups：将分组后的groups转为dict\n",
    "# 可以字典索引方法来查看groups里的元素\n",
    "\n",
    "sz = grouped.size()\n",
    "print(sz,'\\t',type(sz))\n",
    "print(\"----------------------------------------------------------------------------------\")\n",
    "# .size()：查看分组后的长度\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'A':['foo', 'bar', 'foo', 'bar','foo', 'bar', 'foo', 'foo'],\n",
    "    'B':['one', 'one', 'two', 'three', 'two', 'two', 'one', 'three'],\n",
    "    'C':np.random.randn(8),\n",
    "    'D':np.random.randn(8)\n",
    "})\n",
    "grouped = df.groupby(['A','B']).groups\n",
    "print(df)\n",
    "print(grouped)\n",
    "print(grouped[('foo','three')])\n",
    "# 按照两个列进行分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      data1     data2 key1 key2\n",
      "0  0.891535  0.453727    a  one\n",
      "1  0.549994  0.573625    b  two\n",
      "data1    float64\n",
      "data2    float64\n",
      "key1      object\n",
      "key2      object\n",
      "dtype: object\n",
      "-------------------------------------------------------------------\n",
      "float64\n",
      "      data1     data2\n",
      "0  0.891535  0.453727\n",
      "1  0.549994  0.573625\n",
      "#####\n",
      "object\n",
      "  key1 key2\n",
      "0    a  one\n",
      "1    b  two\n",
      "#####\n"
     ]
    }
   ],
   "source": [
    "# 其他轴上的分组\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'data1':np.random.rand(2),\n",
    "    'data2':np.random.rand(2),\n",
    "    'key1':['a','b'],\n",
    "    'key2':['one','two']\n",
    "})\n",
    "print(df)\n",
    "print(df.dtypes)\n",
    "print(\"-------------------------------------------------------------------\")\n",
    "\n",
    "for n,p in df.groupby(df.dtypes, axis = 1):\n",
    "    print(n)\n",
    "    print(p)\n",
    "    print(\"#####\")\n",
    "# 按照值的类型分列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    a   b   c   d\n",
      "0   0   1   2   3\n",
      "1   4   5   6   7\n",
      "2   8   9  10  11\n",
      "3  12  13  14  15\n",
      "-------------------------------------------------------------------\n",
      "   one  two\n",
      "0    1    5\n",
      "1    9   13\n",
      "2   17   21\n",
      "3   25   29\n",
      "-------------------------------------------------------------------\n",
      "a      one\n",
      "b      one\n",
      "c      two\n",
      "d      two\n",
      "e    three\n",
      "dtype: object \n",
      "\n",
      "one      2\n",
      "three    1\n",
      "two      2\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# 通过字典或者Series来进行分组\n",
    "\n",
    "df = pd.DataFrame(\n",
    "np.arange(16).reshape(4,4),\n",
    "columns = list('abcd')\n",
    ")\n",
    "print(df)\n",
    "print(\"-------------------------------------------------------------------\")\n",
    "\n",
    "mapping = {'a':'one','b':'one','c':'two','d':'two','e':'three'}\n",
    "by_column = df.groupby(mapping, axis = 1)\n",
    "print(by_column.sum())\n",
    "print(\"-------------------------------------------------------------------\")\n",
    "# mapping中，a、b列对应的为one，c、d列对应的为two，以字典来分组\n",
    "\n",
    "s = pd.Series(mapping)\n",
    "print(s,'\\n')\n",
    "print(s.groupby(s).count())\n",
    "# s中，index中a、b对应的为one，c、d对应的为two，以Series来分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      a   b   c   d\n",
      "abc   0   1   2   3\n",
      "bcd   4   5   6   7\n",
      "aa    8   9  10  11\n",
      "b    12  13  14  15 \n",
      "\n",
      "    a   b   c   d\n",
      "1  12  13  14  15\n",
      "2   8   9  10  11\n",
      "3   4   6   8  10\n"
     ]
    }
   ],
   "source": [
    "# 通过函数分组\n",
    "\n",
    "df = pd.DataFrame(\n",
    "    np.arange(16).reshape(4,4),\n",
    "    index = ['abc','bcd','aa','b'],\n",
    "    columns = list('abcd')\n",
    ")\n",
    "print(df,'\\n')\n",
    "print(df.groupby(len).sum())\n",
    "# 按照字母长度分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<pandas.core.groupby.SeriesGroupBy object at 0x0000015194465390>\n",
      "1    1\n",
      "2    2\n",
      "3    3\n",
      "dtype: int64 → first：非NaN的第一个值\n",
      "\n",
      "1    10\n",
      "2    20\n",
      "3    30\n",
      "dtype: int64 → last：非NaN的最后一个值\n",
      "\n",
      "1    11\n",
      "2    22\n",
      "3    33\n",
      "dtype: int64 → sum：非NaN的和\n",
      "\n",
      "1     5.5\n",
      "2    11.0\n",
      "3    16.5\n",
      "dtype: float64 → mean：非NaN的平均值\n",
      "\n",
      "1     5.5\n",
      "2    11.0\n",
      "3    16.5\n",
      "dtype: float64 → median：非NaN的算术中位数\n",
      "\n",
      "1    2\n",
      "2    2\n",
      "3    2\n",
      "dtype: int64 → count：非NaN的值\n",
      "\n",
      "1    1\n",
      "2    2\n",
      "3    3\n",
      "dtype: int64 → min、max：非NaN的最小值、最大值\n",
      "\n",
      "1     6.363961\n",
      "2    12.727922\n",
      "3    19.091883\n",
      "dtype: float64 → std，var：非NaN的标准差和方差\n",
      "\n",
      "1    10\n",
      "2    40\n",
      "3    90\n",
      "dtype: int64 → prod：非NaN的积\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 分组计算函数方法\n",
    "\n",
    "s = pd.Series([1, 2, 3, 10, 20, 30], index = [1, 2, 3, 1, 2, 3])\n",
    "grouped = s.groupby(level=0)  # 唯一索引用.groupby(level=0)，将同一个index的分为一组\n",
    "print(grouped)\n",
    "print(grouped.first(),'→ first：非NaN的第一个值\\n')\n",
    "print(grouped.last(),'→ last：非NaN的最后一个值\\n')\n",
    "print(grouped.sum(),'→ sum：非NaN的和\\n')\n",
    "print(grouped.mean(),'→ mean：非NaN的平均值\\n')\n",
    "print(grouped.median(),'→ median：非NaN的算术中位数\\n')\n",
    "print(grouped.count(),'→ count：非NaN的值\\n')\n",
    "print(grouped.min(),'→ min、max：非NaN的最小值、最大值\\n')\n",
    "print(grouped.std(),'→ std，var：非NaN的标准差和方差\\n')\n",
    "print(grouped.prod(),'→ prod：非NaN的积\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   a         b         c         d\n",
      "0  1  0.807284  0.359224  0.581317\n",
      "1  1  0.217221  0.574080  0.447412\n",
      "2  2  0.733367  0.612369  0.373231\n",
      "3  2  0.982862  0.816360  0.143730\n",
      "          b                   c                   d          \n",
      "       mean       sum      mean       sum      mean       sum\n",
      "a                                                            \n",
      "1  0.512252  1.024505  0.466652  0.933304  0.514365  1.028729\n",
      "2  0.858115  1.716229  0.714364  1.428729  0.258480  0.516961\n",
      "    result1   result2\n",
      "a                    \n",
      "1  0.512252  1.024505\n",
      "2  0.858115  1.716229\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "d:\\python\\lib\\site-packages\\ipykernel_launcher.py:12: FutureWarning: using a dict on a Series for aggregation\n",
      "is deprecated and will be removed in a future version\n",
      "  if sys.path[0] == '':\n"
     ]
    }
   ],
   "source": [
    "# 多函数计算：agg()\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'a':[1,1,2,2],\n",
    "    'b':np.random.rand(4),\n",
    "    'c':np.random.rand(4),\n",
    "    'd':np.random.rand(4)\n",
    "})\n",
    "print(df)\n",
    "print(df.groupby('a').agg(['mean',np.sum]))\n",
    "print(df.groupby('a')['b'].agg({'result1':np.mean,\n",
    "                               'result2':np.sum}))\n",
    "# 函数写法可以用str，或者np.方法\n",
    "# 可以通过list，dict传入，当用dict时，key名为columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.19---分组转换及一般性“拆分-应用-合并”\n",
    "## transform / apply"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      data1     data2 key1 key2\n",
      "0  0.792601  0.877711    a  one\n",
      "1  0.882216  0.545468    a  two\n",
      "2  0.205809  0.699231    b  one\n",
      "3  0.871578  0.547097    b  two\n",
      "4  0.055876  0.544955    a  one\n",
      "         data1     data2\n",
      "key1                    \n",
      "a     0.576898  0.656045\n",
      "b     0.538693  0.623164\n",
      "   mean_data1_x  mean_data2_x mean_key1 mean_key2  mean_data1_y  mean_data2_y\n",
      "0      0.792601      0.877711         a       one      0.576898      0.656045\n",
      "1      0.882216      0.545468         a       two      0.576898      0.656045\n",
      "4      0.055876      0.544955         a       one      0.576898      0.656045\n",
      "2      0.205809      0.699231         b       one      0.538693      0.623164\n",
      "3      0.871578      0.547097         b       two      0.538693      0.623164\n",
      "------------------------------------------------------------------------------------------------\n",
      "         data1     data2\n",
      "key2                    \n",
      "one   0.351428  0.707299\n",
      "two   0.876897  0.546283\n",
      "      data1     data2\n",
      "0  0.351428  0.707299\n",
      "1  0.876897  0.546283\n",
      "2  0.351428  0.707299\n",
      "3  0.876897  0.546283\n",
      "4  0.351428  0.707299\n"
     ]
    }
   ],
   "source": [
    "# 数据分组转换 transform\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'data1':np.random.rand(5),\n",
    "    'data2':np.random.rand(5),\n",
    "    'key1':list('aabba'),\n",
    "    'key2':['one','two','one','two','one']\n",
    "})\n",
    "k_mean = df.groupby('key1').mean()\n",
    "print(df)\n",
    "print(k_mean)\n",
    "print(pd.merge(df,k_mean,left_on = 'key1',right_index = True).add_prefix('mean_'))             # add_prefix('mean_'): 添加前缀\n",
    "print(\"------------------------------------------------------------------------------------------------\")\n",
    "# 通过分组、合并，得到一个包含均值的Dataframe\n",
    "\n",
    "print(df.groupby('key2').mean())                 # 按照key2分组求均值\n",
    "print(df.groupby('key2').transform(np.mean))\n",
    "# data1、data2每个位置元素取对应分组列的均值\n",
    "# 字符串不能进行计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               data1     data2\n",
      "key1                          \n",
      "a    count  3.000000  3.000000\n",
      "     mean   0.683143  0.406082\n",
      "     std    0.281340  0.219219\n",
      "     min    0.359297  0.279412\n",
      "     25%    0.591062  0.279516\n",
      "     50%    0.822828  0.279620\n",
      "     75%    0.845067  0.469418\n",
      "     max    0.867305  0.659215\n",
      "b    count  2.000000  2.000000\n",
      "     mean   0.394690  0.305851\n",
      "     std    0.358097  0.084328\n",
      "     min    0.141477  0.246222\n",
      "     25%    0.268083  0.276037\n",
      "     50%    0.394690  0.305851\n",
      "     75%    0.521296  0.335666\n",
      "     max    0.647902  0.365480\n",
      "           data1     data2 key1 key2\n",
      "key1                                \n",
      "a    0  0.359297  0.279412    a  one\n",
      "     1  0.867305  0.659215    a  two\n",
      "b    2  0.647902  0.246222    b  one\n",
      "     3  0.141477  0.365480    b  two \n",
      "\n",
      "key1   \n",
      "a     0    0.279412\n",
      "      1    0.659215\n",
      "      4    0.279620\n",
      "b     2    0.246222\n",
      "      3    0.365480\n",
      "Name: data2, dtype: float64\n",
      "<class 'pandas.core.series.Series'>\n"
     ]
    }
   ],
   "source": [
    "# 一般化Groupby方法：apply\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'data1':np.random.rand(5),\n",
    "    'data2':np.random.rand(5),\n",
    "    'key1':list('aabba'),\n",
    "    'key2':['one','two','one','two','one']\n",
    "})\n",
    "print(df.groupby('key1').apply(lambda x:x.describe()))\n",
    "# apply直接运行其中的函数\n",
    "# 这里为匿名函数，直接描述分组后的统计量\n",
    "\n",
    "def f_df1(d,n):\n",
    "    return(d.sort_index()[:n])\n",
    "def f_df2(d,k1):\n",
    "    return(d[k1])\n",
    "print(df.groupby('key1').apply(f_df1,2),'\\n')\n",
    "print(df.groupby('key1').apply(f_df2,'data2'))\n",
    "print(type(df.groupby('key1').apply(f_df2,'data2')))\n",
    "# f_df1函数：返回排序后的前n行数据\n",
    "# f_df2函数：返回分组后表的k1列，结果为Series，层次化索引\n",
    "# 直接运行f_df函数\n",
    "# 参数直接写在后面，也可以为.apply(f_df,n = 2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.20---透视表及交叉表\n",
    "## 类似excel数据透视 --- pivot table / crosstab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        date key    values\n",
      "0 2017-05-01   a  7.770042\n",
      "1 2017-05-02   b  0.266616\n",
      "2 2017-05-03   c  5.986476\n",
      "3 2017-05-01   d  2.121103\n",
      "4 2017-05-02   a  4.707652\n",
      "5 2017-05-03   b  1.820101\n",
      "6 2017-05-01   c  1.207413\n",
      "7 2017-05-02   d  6.924021\n",
      "8 2017-05-03   a  1.079852\n",
      "----------------------------------------------------------------------------------------------------------------\n",
      "key                a         b         c         d\n",
      "date                                              \n",
      "2017-05-01  7.770042       NaN  1.207413  2.121103\n",
      "2017-05-02  4.707652  0.266616       NaN  6.924021\n",
      "2017-05-03  1.079852  1.820101  5.986476       NaN\n",
      "----------------------------------------------------------------------------------------------------------------\n",
      "                values\n",
      "date       key        \n",
      "2017-05-01 a       1.0\n",
      "           c       1.0\n",
      "           d       1.0\n",
      "2017-05-02 a       1.0\n",
      "           b       1.0\n",
      "           d       1.0\n",
      "2017-05-03 a       1.0\n",
      "           b       1.0\n",
      "           c       1.0\n",
      "----------------------------------------------------------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "# 透视表：pivot_table\n",
    "# pd.pivot_table(data, values=None, index=None, columns=None, aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All')\n",
    "\n",
    "date = ['2017-5-1','2017-5-2','2017-5-3']*3\n",
    "rng = pd.to_datetime(date)\n",
    "df = pd.DataFrame({\n",
    "    'date':rng,\n",
    "    'key':list('abcdabcda'),\n",
    "    'values':np.random.rand(9)*10\n",
    "})\n",
    "print(df)\n",
    "print('----------------------------------------------------------------------------------------------------------------')\n",
    "\n",
    "\n",
    "print(pd.pivot_table(df, values = 'values', index = 'date', columns = 'key', aggfunc=np.sum))  # 也可以写 aggfunc='sum' 写成字符串形式当然也是可以的\n",
    "print('----------------------------------------------------------------------------------------------------------------')\n",
    "# data：DataFrame对象\n",
    "# values：要聚合的列或列的列表\n",
    "# index：数据透视表的index，从原数据的列中筛选\n",
    "# columns：数据透视表的columns，从原数据的列中筛选\n",
    "# aggfunc：用于聚合的函数，默认为numpy.mean，支持numpy计算方法\n",
    "\n",
    "\n",
    "print(pd.pivot_table(df, values = 'values', index = ['date','key'], aggfunc=len))\n",
    "print('----------------------------------------------------------------------------------------------------------------')\n",
    "# 这里就分别以date、key共同做数据透视，值为values：统计不同（date，key）情况下values的平均值\n",
    "# aggfunc=len(或者count)：计数  Note：此处务必要注意！！！"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A  B    C\n",
      "0  1  3  1.0\n",
      "1  2  3  1.0\n",
      "2  2  4  NaN\n",
      "3  2  4  1.0\n",
      "4  2  4  1.0\n",
      "--------------------------------------------------------------------------------------------------------------\n",
      "B  3  4\n",
      "A      \n",
      "1  1  0\n",
      "2  1  3\n",
      "--------------------------------------------------------------------------------------------------------------\n",
      "B    3    4\n",
      "A          \n",
      "1  0.2  0.0\n",
      "2  0.2  0.6\n",
      "--------------------------------------------------------------------------------------------------------------\n",
      "B    3    4\n",
      "A          \n",
      "1  1.0  NaN\n",
      "2  1.0  2.0\n",
      "--------------------------------------------------------------------------------------------------------------\n",
      "B      3    4  All\n",
      "A                 \n",
      "1    1.0  NaN  1.0\n",
      "2    1.0  2.0  3.0\n",
      "All  2.0  2.0  4.0\n",
      "--------------------------------------------------------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "# 交叉表：crosstab\n",
    "# 默认情况下，crosstab计算因子的频率表，比如用于str的数据透视分析     这里需要注意一下！！！\n",
    "# pd.crosstab(index, columns, values=None, rownames=None, colnames=None, aggfunc=None, margins=False, dropna=True, normalize=False)\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'A': [1, 2, 2, 2, 2],\n",
    "    'B': [3, 3, 4, 4, 4],\n",
    "    'C': [1, 1, np.nan, 1, 1]\n",
    "})\n",
    "print(df)\n",
    "print('--------------------------------------------------------------------------------------------------------------')\n",
    "\n",
    "print(pd.crosstab(df['A'],df['B']))\n",
    "print('--------------------------------------------------------------------------------------------------------------')\n",
    "# 如果crosstab只接收两个Series，它将提供一个频率表。\n",
    "# 用A的唯一值，统计B唯一值的出现次数\n",
    "\n",
    "print(pd.crosstab(df['A'],df['B'],normalize=True))\n",
    "print('--------------------------------------------------------------------------------------------------------------')\n",
    "# normalize：默认False，将所有值除以值的总和进行归一化 → 为True时候显示百分比\n",
    "\n",
    "print(pd.crosstab(df['A'],df['B'],values=df['C'],aggfunc=np.sum))\n",
    "print('--------------------------------------------------------------------------------------------------------------')\n",
    "# values：可选，根据因子聚合的值数组\n",
    "# aggfunc：可选，如果未传递values数组，则计算频率表，如果传递数组，则按照指定计算\n",
    "# 这里相当于以A和B界定分组，计算出每组中第三个系列C的值\n",
    "\n",
    "print(pd.crosstab(df['A'],df['B'],values=df['C'],aggfunc=np.sum, margins=True))\n",
    "print('--------------------------------------------------------------------------------------------------------------')\n",
    "# margins：布尔值，默认值False，添加行/列边距（小计）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2.21---数据读取\n",
    "## 核心：read_table , read_csv , read_excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Empty DataFrame\n",
      "Columns: [20171201, 20171203, 20171204, 20171205, 20171206, 20171207, 20171208, 20171209, 20171210]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "# 读取普通分隔数据：read_table\n",
    "# 可以读取txt，csv\n",
    "\n",
    "import os\n",
    "os.chdir(r'D:\\Python练习')\n",
    "\n",
    "data1 = pd.read_table('data1.txt', delimiter=',',header = 0, index_col=1)\n",
    "print(data1)\n",
    "# delimiter：用于拆分的字符，也可以用sep：sep = ','\n",
    "# header：用做列名的序号，默认为0（第一行）\n",
    "# index_col：指定某列为行索引，否则自动索引0, 1, .....\n",
    "\n",
    "# read_table主要用于读取简单的数据，txt/csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                                                                                                                                                       classify\n",
      "美食 望蓉城老坛酸菜鱼(合生汇店)   我要点评                        该商户暂无星级 人均                                        ￥125 翔殷路1099号合生汇5F          口味8.3                                环境8.4    ...\n",
      "   泰国街边料理           74                    条点评   准四星商户   人均                                        ￥48  黄兴路合生汇B2美食集市内          口味7.4                                环境7.6    ...\n",
      "   壹面如故(苏宁生活广场店)    265                    条点评  准四星商户   人均                                        ￥21  邯郸路585号苏宁生活广场B1层       口味7.0                                环境7.2    ...\n",
      "   鮨谷•Aburiya(合生汇店) 2748                    条点评 准五星商户   人均                                        ￥142 翔殷路1099号合生广场5楼23、28铺位  口味8.9                                环境8.5    ...\n",
      "   我们的烤肉我们的馕        5                    条点评    准四星商户   人均                                    -        邯郸路399-D3号             口味7.5                                环境6.8    ...\n"
     ]
    }
   ],
   "source": [
    "# 读取csv数据：read_csv\n",
    "# 先熟悉一下excel怎么导出csv\n",
    "\n",
    "data2 = pd.read_csv('商铺数据.csv',engine = 'python',encoding = 'utf-8')\n",
    "print(data2.head())\n",
    "# engine：使用的分析引擎。可以选择C或者是python。C引擎快但是Python引擎功能更加完备。\n",
    "# encoding：指定字符集类型，即编码，通常指定为'utf-8'\n",
    "\n",
    "# 大多数情况先将excel导出csv，再读取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "d:\\python\\lib\\site-packages\\pandas\\util\\_decorators.py:118: FutureWarning: The `sheetname` keyword is deprecated, use `sheet_name` instead\n",
      "  return func(*args, **kwargs)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      省级政区代码    省级政区名称  地市级政区代码   地市级政区名称    年份 党委书记姓名  出生年份  出生月份  籍贯省份代码  \\\n",
      "0     130000       河北省   130100      石家庄市  2000    陈来立   NaN   NaN     NaN   \n",
      "1     130000       河北省   130100      石家庄市  2001    吴振华   NaN   NaN     NaN   \n",
      "2     130000       河北省   130100      石家庄市  2002    吴振华   NaN   NaN     NaN   \n",
      "3     130000       河北省   130100      石家庄市  2003    吴振华   NaN   NaN     NaN   \n",
      "4     130000       河北省   130100      石家庄市  2004    吴振华   NaN   NaN     NaN   \n",
      "5     130000       河北省   130100      石家庄市  2005    吴振华   NaN   NaN     NaN   \n",
      "6     130000       河北省   130100      石家庄市  2006    吴振华   NaN   NaN     NaN   \n",
      "7     130000       河北省   130100      石家庄市  2007    吴显国   NaN   NaN     NaN   \n",
      "8     130000       河北省   130100      石家庄市  2008    吴显国   NaN   NaN     NaN   \n",
      "9     130000       河北省   130100      石家庄市  2009     车俊   NaN   NaN     NaN   \n",
      "10    130000       河北省   130100      石家庄市  2010    孙瑞彬   NaN   NaN     NaN   \n",
      "11    130000       河北省   130200       唐山市  2000    白润璋   NaN   NaN     NaN   \n",
      "12    130000       河北省   130200       唐山市  2001    白润璋   NaN   NaN     NaN   \n",
      "13    130000       河北省   130200       唐山市  2002    白润璋   NaN   NaN     NaN   \n",
      "14    130000       河北省   130200       唐山市  2003     张和   NaN   NaN     NaN   \n",
      "15    130000       河北省   130200       唐山市  2004     张和   NaN   NaN     NaN   \n",
      "16    130000       河北省   130200       唐山市  2005     张和   NaN   NaN     NaN   \n",
      "17    130000       河北省   130200       唐山市  2006     张和   NaN   NaN     NaN   \n",
      "18    130000       河北省   130200       唐山市  2007     赵勇   NaN   NaN     NaN   \n",
      "19    130000       河北省   130200       唐山市  2008     赵勇   NaN   NaN     NaN   \n",
      "20    130000       河北省   130200       唐山市  2009     赵勇   NaN   NaN     NaN   \n",
      "21    130000       河北省   130200       唐山市  2010     赵勇   NaN   NaN     NaN   \n",
      "22    130000       河北省   130300      秦皇岛市  2000    王建忠   NaN   NaN     NaN   \n",
      "23    130000       河北省   130300      秦皇岛市  2001    王建忠   NaN   NaN     NaN   \n",
      "24    130000       河北省   130300      秦皇岛市  2002    王建忠   NaN   NaN     NaN   \n",
      "25    130000       河北省   130300      秦皇岛市  2003    宋长瑞   NaN   NaN     NaN   \n",
      "26    130000       河北省   130300      秦皇岛市  2004    宋长瑞   NaN   NaN     NaN   \n",
      "27    130000       河北省   130300      秦皇岛市  2005    宋长瑞   NaN   NaN     NaN   \n",
      "28    130000       河北省   130300      秦皇岛市  2006    宋长瑞   NaN   NaN     NaN   \n",
      "29    130000       河北省   130300      秦皇岛市  2007    王三堂   NaN   NaN     NaN   \n",
      "...      ...       ...      ...       ...   ...    ...   ...   ...     ...   \n",
      "3633  650000  新疆维吾尔自治区   654000  伊犁哈萨克自治州  2003    NaN   NaN   NaN     NaN   \n",
      "3634  650000  新疆维吾尔自治区   654000  伊犁哈萨克自治州  2004    NaN   NaN   NaN     NaN   \n",
      "3635  650000  新疆维吾尔自治区   654000  伊犁哈萨克自治州  2005    NaN   NaN   NaN     NaN   \n",
      "3636  650000  新疆维吾尔自治区   654000  伊犁哈萨克自治州  2006    NaN   NaN   NaN     NaN   \n",
      "3637  650000  新疆维吾尔自治区   654000  伊犁哈萨克自治州  2007    NaN   NaN   NaN     NaN   \n",
      "3638  650000  新疆维吾尔自治区   654000  伊犁哈萨克自治州  2008    NaN   NaN   NaN     NaN   \n",
      "3639  650000  新疆维吾尔自治区   654000  伊犁哈萨克自治州  2009    NaN   NaN   NaN     NaN   \n",
      "3640  650000  新疆维吾尔自治区   654000  伊犁哈萨克自治州  2010    NaN   NaN   NaN     NaN   \n",
      "3641  650000  新疆维吾尔自治区   654200      塔城地区  2000    NaN   NaN   NaN     NaN   \n",
      "3642  650000  新疆维吾尔自治区   654200      塔城地区  2001    NaN   NaN   NaN     NaN   \n",
      "3643  650000  新疆维吾尔自治区   654200      塔城地区  2002    NaN   NaN   NaN     NaN   \n",
      "3644  650000  新疆维吾尔自治区   654200      塔城地区  2003    NaN   NaN   NaN     NaN   \n",
      "3645  650000  新疆维吾尔自治区   654200      塔城地区  2004    NaN   NaN   NaN     NaN   \n",
      "3646  650000  新疆维吾尔自治区   654200      塔城地区  2005    NaN   NaN   NaN     NaN   \n",
      "3647  650000  新疆维吾尔自治区   654200      塔城地区  2006    NaN   NaN   NaN     NaN   \n",
      "3648  650000  新疆维吾尔自治区   654200      塔城地区  2007    NaN   NaN   NaN     NaN   \n",
      "3649  650000  新疆维吾尔自治区   654200      塔城地区  2008    NaN   NaN   NaN     NaN   \n",
      "3650  650000  新疆维吾尔自治区   654200      塔城地区  2009    NaN   NaN   NaN     NaN   \n",
      "3651  650000  新疆维吾尔自治区   654200      塔城地区  2010    NaN   NaN   NaN     NaN   \n",
      "3652  650000  新疆维吾尔自治区   654300     阿勒泰地区  2000    NaN   NaN   NaN     NaN   \n",
      "3653  650000  新疆维吾尔自治区   654300     阿勒泰地区  2001    NaN   NaN   NaN     NaN   \n",
      "3654  650000  新疆维吾尔自治区   654300     阿勒泰地区  2002    NaN   NaN   NaN     NaN   \n",
      "3655  650000  新疆维吾尔自治区   654300     阿勒泰地区  2003    NaN   NaN   NaN     NaN   \n",
      "3656  650000  新疆维吾尔自治区   654300     阿勒泰地区  2004    NaN   NaN   NaN     NaN   \n",
      "3657  650000  新疆维吾尔自治区   654300     阿勒泰地区  2005    NaN   NaN   NaN     NaN   \n",
      "3658  650000  新疆维吾尔自治区   654300     阿勒泰地区  2006    NaN   NaN   NaN     NaN   \n",
      "3659  650000  新疆维吾尔自治区   654300     阿勒泰地区  2007    NaN   NaN   NaN     NaN   \n",
      "3660  650000  新疆维吾尔自治区   654300     阿勒泰地区  2008    NaN   NaN   NaN     NaN   \n",
      "3661  650000  新疆维吾尔自治区   654300     阿勒泰地区  2009    NaN   NaN   NaN     NaN   \n",
      "3662  650000  新疆维吾尔自治区   654300     阿勒泰地区  2010    NaN   NaN   NaN     NaN   \n",
      "\n",
      "     籍贯省份名称  ...    民族   教育 是否是党校教育（是=1，否=0） 专业：人文 专业：社科  专业：理工  专业：农科  专业：医科  \\\n",
      "0       NaN  ...   NaN   硕士              1.0   NaN   NaN    NaN    NaN    NaN   \n",
      "1       NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "2       NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "3       NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "4       NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "5       NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "6       NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "7       NaN  ...   NaN   硕士              1.0   0.0   1.0    0.0    0.0    0.0   \n",
      "8       NaN  ...   NaN   硕士              1.0   0.0   1.0    0.0    0.0    0.0   \n",
      "9       NaN  ...   NaN   本科              1.0   0.0   1.0    0.0    0.0    0.0   \n",
      "10      NaN  ...   NaN   硕士              1.0   0.0   1.0    0.0    0.0    0.0   \n",
      "11      NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "12      NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "13      NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "14      NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "15      NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "16      NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "17      NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "18      NaN  ...   NaN   博士              0.0   0.0   1.0    0.0    0.0    0.0   \n",
      "19      NaN  ...   NaN   博士              0.0   0.0   1.0    0.0    0.0    0.0   \n",
      "20      NaN  ...   NaN   博士              0.0   0.0   1.0    0.0    0.0    0.0   \n",
      "21      NaN  ...   NaN   博士              0.0   0.0   1.0    0.0    0.0    0.0   \n",
      "22      NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "23      NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "24      NaN  ...   NaN   本科              0.0   0.0   0.0    1.0    0.0    0.0   \n",
      "25      NaN  ...   NaN   硕士              1.0   0.0   1.0    0.0    0.0    0.0   \n",
      "26      NaN  ...   NaN   硕士              1.0   0.0   1.0    0.0    0.0    0.0   \n",
      "27      NaN  ...   NaN   硕士              1.0   0.0   1.0    0.0    0.0    0.0   \n",
      "28      NaN  ...   NaN   硕士              1.0   0.0   1.0    0.0    0.0    0.0   \n",
      "29      NaN  ...   NaN   硕士              1.0   0.0   1.0    0.0    0.0    0.0   \n",
      "...     ...  ...   ...  ...              ...   ...   ...    ...    ...    ...   \n",
      "3633    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3634    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3635    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3636    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3637    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3638    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3639    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3640    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3641    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3642    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3643    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3644    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3645    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3646    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3647    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3648    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3649    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3650    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3651    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3652    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3653    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3654    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3655    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3656    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3657    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3658    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3659    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3660    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3661    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "3662    NaN  ...   NaN  NaN              NaN   NaN   NaN    NaN    NaN    NaN   \n",
      "\n",
      "      入党年份  工作年份  \n",
      "0      NaN   NaN  \n",
      "1      NaN   NaN  \n",
      "2      NaN   NaN  \n",
      "3      NaN   NaN  \n",
      "4      NaN   NaN  \n",
      "5      NaN   NaN  \n",
      "6      NaN   NaN  \n",
      "7      NaN   NaN  \n",
      "8      NaN   NaN  \n",
      "9      NaN   NaN  \n",
      "10     NaN   NaN  \n",
      "11     NaN   NaN  \n",
      "12     NaN   NaN  \n",
      "13     NaN   NaN  \n",
      "14     NaN   NaN  \n",
      "15     NaN   NaN  \n",
      "16     NaN   NaN  \n",
      "17     NaN   NaN  \n",
      "18     NaN   NaN  \n",
      "19     NaN   NaN  \n",
      "20     NaN   NaN  \n",
      "21     NaN   NaN  \n",
      "22     NaN   NaN  \n",
      "23     NaN   NaN  \n",
      "24     NaN   NaN  \n",
      "25     NaN   NaN  \n",
      "26     NaN   NaN  \n",
      "27     NaN   NaN  \n",
      "28     NaN   NaN  \n",
      "29     NaN   NaN  \n",
      "...    ...   ...  \n",
      "3633   NaN   NaN  \n",
      "3634   NaN   NaN  \n",
      "3635   NaN   NaN  \n",
      "3636   NaN   NaN  \n",
      "3637   NaN   NaN  \n",
      "3638   NaN   NaN  \n",
      "3639   NaN   NaN  \n",
      "3640   NaN   NaN  \n",
      "3641   NaN   NaN  \n",
      "3642   NaN   NaN  \n",
      "3643   NaN   NaN  \n",
      "3644   NaN   NaN  \n",
      "3645   NaN   NaN  \n",
      "3646   NaN   NaN  \n",
      "3647   NaN   NaN  \n",
      "3648   NaN   NaN  \n",
      "3649   NaN   NaN  \n",
      "3650   NaN   NaN  \n",
      "3651   NaN   NaN  \n",
      "3652   NaN   NaN  \n",
      "3653   NaN   NaN  \n",
      "3654   NaN   NaN  \n",
      "3655   NaN   NaN  \n",
      "3656   NaN   NaN  \n",
      "3657   NaN   NaN  \n",
      "3658   NaN   NaN  \n",
      "3659   NaN   NaN  \n",
      "3660   NaN   NaN  \n",
      "3661   NaN   NaN  \n",
      "3662   NaN   NaN  \n",
      "\n",
      "[3663 rows x 23 columns]\n"
     ]
    }
   ],
   "source": [
    "# 读取excel数据：read_excel\n",
    "\n",
    "data3 = pd.read_excel('地市级党委书记数据库（2000-10）.xlsx',sheetname='中国人民共和国地市级党委书记数据库（2000-10）',header=0)\n",
    "print(data3)\n",
    "# io ：文件路径。\n",
    "# sheetname：返回多表使用sheetname=[0,1],若sheetname=None是返回全表 → ① int/string 返回的是dataframe ②而none和list返回的是dict\n",
    "# header：指定列名行，默认0，即取第一行\n",
    "# index_col：指定列为索引列，也可以使用u”strings”"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
